# -*- coding: utf-8 -*-

from openerp import tools
from openerp.osv import fields, osv


class stock_picking_delivery_table(osv.osv):
    # 采购单产品毛利统计
    _name = 'stock.picking.delivery.table'
    _description = u'仓库明日发货数量统计'

    _columns = {
        'sale_order_id': fields.many2one('sale.order', string=u'销售订单', readonly=True),
        'product_code': fields.char(u'产品编号', readonly=True),
        'product_name': fields.char(u'产品名称', readonly=True),
        'product_uom_qty': fields.float(u'产品数量（本销售订单未发）', readonly=True),
        'tomorrow_need_qty': fields.float(u'产品发货数合计（明天）', readonly=True),
        'quant_qty': fields.float(u'库存数（除去第三天锁住的）', readonly=True),
        'not_shipped_qty': fields.float(u'当天要发（含以前未发）', readonly=True),
        'locked': fields.float(u'第三天及以后锁住', readonly=True),
    }

class examine_stock_wizard(osv.osv_memory):
    _name = 'examine.stock.wizard'
    _columns = {
        'date': fields.date(u'查看对应日期')
    }

    def create_stock_picking_delivery(self, cr, uid, ids, context=None):
        delivery_table = self.pool.get("stock.picking.delivery.table")
        for examine_stock_row in self.browse(cr, uid, ids, context=context):
            location_ids = self.pool.get('stock.location').get_child_location_ids(cr, uid, 12, context=context)
            str_locations = str(location_ids).replace('[', '(').replace(']', ')')
            sql = """
             SELECT
              distinct s_order.id                                                                    AS sale_order_id,
              p_template.name                                                               AS product_name,
              p_product.default_code                                                        AS product_code,
              COALESCE(order_line.product_uom_qty, 0) - COALESCE(order_line.shipped_qty, 0) AS product_uom_qty,
              COALESCE(order_inner.all_qty, 0)                                              AS tomorrow_need_qty,
              COALESCE(s_quant_innner.qty, 0) - 
              (COALESCE(lock_quant_order.qty, 0) +  COALESCE(lock_quant_move.qty, 0))       AS quant_qty,
              today_order.not_shipped_qty                                                   AS not_shipped_qty,
               COALESCE(lock_quant_order.qty, 0) +  COALESCE(lock_quant_move.qty, 0)                 AS locked

            FROM sale_order_line order_line
              INNER JOIN sale_order s_order ON order_line.order_id = s_order.id
              INNER JOIN product_product p_product ON p_product.id = order_line.product_id
              INNER JOIN product_template p_template ON p_template.id = p_product.product_tmpl_id
              INNER JOIN order_type o_type ON s_order.order_type_id = o_type.id
              LEFT JOIN stock_move s_move ON s_move.so_line_id = order_line.id

              LEFT JOIN (SELECT
                           sum(order_line_inner.product_uom_qty) all_qty,
                           order_line_inner.product_id
                         FROM sale_order_line order_line_inner
                           INNER JOIN sale_order s_order_inner ON order_line_inner.order_id = s_order_inner.id
                           INNER JOIN order_type o_type_inner ON s_order_inner.order_type_id = o_type_inner.id
                         WHERE s_order_inner.expected_delivery_time =
                               '%s'::TIMESTAMP
                               AND s_order_inner.state NOT IN ('cancel', 'draft', 'done') AND s_order_inner.is_card = FALSE AND
                               s_order_inner.real_so_id IS NULL AND s_order_inner.is_project = FALSE AND
                               o_type_inner.order_type_code IN ('normal_order', 'card_order')
                         GROUP BY product_id) order_inner ON order_inner.product_id = order_line.product_id
              LEFT JOIN (SELECT
                           sum(s_quant.qty) AS qty,
                           s_quant.product_id
                         FROM stock_quant s_quant
                         WHERE s_quant.location_id in %s
                         GROUP BY s_quant.product_id)
                        s_quant_innner ON s_quant_innner.product_id = order_line.product_id

              LEFT JOIN (SELECT
                              sum(COALESCE(lf_quant.qty, 0)) AS qty,
                              order_line.product_id                                       AS product_id
                            FROM sale_order_line order_line
                              LEFT JOIN stock_quant lf_quant ON order_line.id = lf_quant.so_line_id AND lf_quant.reservation_id = -1
                              INNER JOIN sale_order s_order ON order_line.order_id = s_order.id
                            WHERE s_order.expected_delivery_time > '%s' :: TIMESTAMP AND
                                  s_order.state NOT IN ('cancel', 'draft') AND  lf_quant.location_id in %s
                            GROUP BY order_line.product_id) lock_quant_order ON lock_quant_order.product_id = order_line.product_id
              
              LEFT JOIN (SELECT
                              sum(COALESCE(slf_quant.qty, 0)) AS qty,
                              order_line.product_id                                       AS product_id
                            FROM sale_order_line order_line
                              LEFT JOIN stock_move s_move ON s_move.so_line_id = order_line.id
                              LEFT JOIN stock_quant slf_quant ON s_move.id = slf_quant.reservation_id AND s_move.state='assigned'
                              INNER JOIN sale_order s_order ON order_line.order_id = s_order.id
                            WHERE s_order.expected_delivery_time > '%s' :: TIMESTAMP AND
                                  s_order.state NOT IN ('cancel', 'draft') AND slf_quant.location_id in %s
                            GROUP BY order_line.product_id) lock_quant_move ON lock_quant_move.product_id = order_line.product_id

              LEFT JOIN (SELECT
                           sum(COALESCE(order_line_inner.product_uom_qty, 0) -
                               COALESCE(order_line_inner.shipped_qty, 0)) AS not_shipped_qty,
                           order_line_inner.product_id                    AS product_id
                         FROM sale_order_line order_line_inner
                           INNER JOIN sale_order s_order_inner ON order_line_inner.order_id = s_order_inner.id
                           INNER JOIN order_type o_type_inner ON s_order_inner.order_type_id = o_type_inner.id
                         WHERE s_order_inner.expected_delivery_time < '%s':: TIMESTAMP
                               AND s_order_inner.state NOT IN ('cancel', 'draft', 'done') AND s_order_inner.is_card = FALSE AND
                               s_order_inner.real_so_id IS NULL AND s_order_inner.is_project = FALSE AND
                               o_type_inner.order_type_code IN ('normal_order', 'card_order')
                         GROUP BY product_id) today_order ON today_order.product_id = order_line.product_id
            WHERE s_order.expected_delivery_time = '%s':: TIMESTAMP
                  AND s_order.state NOT IN ('cancel', 'draft', 'done') AND s_order.is_card = FALSE AND order_line.not_shipped_qty
                  AND p_template.type != 'service' AND
                  s_order.real_so_id IS NULL AND s_order.is_project = FALSE AND
                  o_type.order_type_code IN ('normal_order', 'card_order')""" % (
            examine_stock_row.date, str_locations,examine_stock_row.date, str_locations, examine_stock_row.date,
            str_locations, examine_stock_row.date, examine_stock_row.date)
            cr.execute(sql)
            stock_list = cr.dictfetchall()
            delivery_ids = delivery_table.search(cr, uid, [], context=context)
            delivery_table.unlink(cr, uid, delivery_ids, context=context)
            stock_ids = []
            for stock in stock_list:
                stock_ids.append(self.pool.get("stock.picking.delivery.table").create(cr, uid, stock, context=context))
            return {
                'name': u'%s 需要发货' % examine_stock_row.date,
                'type': 'ir.actions.act_window',
                'res_model': 'stock.picking.delivery.table',
                'domain': [('id', 'in', stock_ids)],
                'view_type': 'form',
                'view_mode': 'tree',
            }
